﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace Fotobanka
{
    /// <summary>
    /// Obsluzna trida ktera pracuje s tabulkou user
    /// </summary>
    public class UserManager
    {
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;
        
        /*
         * UserObject new_user = new UserObject();

            new_user.FirstName = fname;
            new_user.LastName = lname;
            new_user.Phone = phone;
            new_user.PasswordHash = pass;
            new_user.GalleryAdmin = Convert.ToByte(gadmin);
            new_user.MobilPhone = mobil;
            new_user.Icq = icq;
            new_user.Skype = skype;
         * */

        /// <summary>
        /// Vybere uzivatele z tabulky User
        /// </summary>
        /// <returns>Id uzivatele</returns>
        public List<UserObject> UserList() {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<UserObject> l = new List<UserObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Users,User_Roles WHERE Users.id=User_Roles.user_id", connection);

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        UserObject new_user = new UserObject();
                        new_user.Id = Convert.ToInt32(reader["id"]);
                        new_user.Email = reader["email"].ToString();
                        new_user.FirstName = reader["first_name"].ToString();
                        new_user.LastName = reader["last_name"].ToString();
                        new_user.Phone = reader["phone"].ToString();
                        new_user.LastLogin = (reader["last_login"] != DBNull.Value)? (DateTime)reader["last_login"] : DateTime.MinValue;
                        new_user.PasswordHash = reader["password_hash"].ToString();
                        new_user.Lock = Convert.ToByte(reader["lock_version"]);
                        new_user.CreatedOn = (DateTime)reader["created_on"];
                        new_user.UpdatedOn = (DateTime)reader["updated_on"];
                        new_user.Authorization = Convert.ToByte(reader["authorization"]);
                        new_user.GalleryAdmin = Convert.ToByte(reader["gallery_admin"]);
                        new_user.MobilPhone = reader["mobil_phone"].ToString();
                        new_user.Icq = Convert.ToInt32(reader["icq"]);
                        new_user.Skype = reader["skype"].ToString();
                        new_user.Role = Convert.ToInt32(reader["role_id"].ToString());
                        l.Add(new_user);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<UserObject>();
        }
        /// <summary>
        /// Vyhleda seznam spravcu vcetne id aktualniho uzivatele (admina)
        /// </summary>
        /// <param name="user_id">id uzivatele</param>
        /// <returns>seznam uzivatelu</returns>
        public List<UserObject> UsersListSpravcuIseSebou(int user_id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<UserObject> l = new List<UserObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM  User_Roles,Users WHERE User_Roles.user_id=Users.id AND User_Roles.role_id=2 UNION SELECT * FROM User_Roles,Users WHERE User_Roles.user_id=Users.id AND Users.id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", user_id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        UserObject new_user = new UserObject();
                        new_user.Id = Convert.ToInt32(reader["user_id"]);
                        new_user.Email = reader["email"].ToString();
                        new_user.FirstName = reader["first_name"].ToString();
                        new_user.LastName = reader["last_name"].ToString();
                        new_user.Phone = reader["phone"].ToString();
                        new_user.LastLogin = (reader["last_login"] != DBNull.Value) ? (DateTime)reader["last_login"] : DateTime.MinValue;
                        new_user.PasswordHash = reader["password_hash"].ToString();
                        new_user.Lock = Convert.ToByte(reader["lock_version"]);
                        new_user.CreatedOn = (DateTime)reader["created_on"];
                        new_user.UpdatedOn = (DateTime)reader["updated_on"];
                        new_user.Authorization = Convert.ToByte(reader["authorization"]);
                        new_user.GalleryAdmin = Convert.ToByte(reader["gallery_admin"]);
                        new_user.MobilPhone = reader["mobil_phone"].ToString();
                        new_user.Icq = Convert.ToInt32(reader["icq"]);
                        new_user.Skype = reader["skype"].ToString();
                        l.Add(new_user);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<UserObject>();
        }
        /// <summary>
        /// Vyhleda seznam obyc uzivatelu
        /// </summary>
        /// <returns>seznam uzivatelu</returns>
        public List<UserObject> UsersListNeadminu()
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<UserObject> l = new List<UserObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM  User_Roles,Users WHERE User_Roles.user_id=Users.id AND User_Roles.role_id=2 UNION SELECT * FROM User_Roles,Users WHERE User_Roles.user_id=Users.id AND User_Roles.role_id<>1", connection);

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        UserObject new_user = new UserObject();
                        new_user.Id = Convert.ToInt32(reader["user_id"]);
                        new_user.Email = reader["email"].ToString();
                        new_user.FirstName = reader["first_name"].ToString();
                        new_user.LastName = reader["last_name"].ToString();
                        new_user.Phone = reader["phone"].ToString();
                        new_user.LastLogin = (reader["last_login"] != DBNull.Value) ? (DateTime)reader["last_login"] : DateTime.MinValue;
                        new_user.PasswordHash = reader["password_hash"].ToString();
                        new_user.Lock = Convert.ToByte(reader["lock_version"]);
                        new_user.CreatedOn = (DateTime)reader["created_on"];
                        new_user.UpdatedOn = (DateTime)reader["updated_on"];
                        new_user.Authorization = Convert.ToByte(reader["authorization"]);
                        new_user.GalleryAdmin = Convert.ToByte(reader["gallery_admin"]);
                        new_user.MobilPhone = reader["mobil_phone"].ToString();
                        new_user.Icq = Convert.ToInt32(reader["icq"]);
                        new_user.Skype = reader["skype"].ToString();
                        l.Add(new_user);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<UserObject>();
        }

        /// <summary>
        /// Vlozi uzivatele do databaze
        /// </summary>
        /// <param name="email">email</param>
        /// <param name="fname">jmeno</param>
        /// <param name="lname">prijmeni</param>
        /// <param name="phone">telefon</param>
        /// <param name="pass">heslo</param>
        /// <param name="gadmin">adminuje skupinu</param>
        /// <param name="auth">autorizovan</param>
        /// <param name="mobil">mobil</param>
        /// <param name="icq">icq</param>
        /// <param name="skype">skype</param>
        public void InsertTodb(string email, string fname,string lname, string phone, string pass, bool gadmin, bool auth, string mobil, int icq, string skype)
        {
            try
            {
	            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
	            {
                    connection.Open();
           
                    command = new SqlCommand("INSERT INTO Users  (email, first_name, last_name, phone, password_hash, created_on, updated_on, [authorization], gallery_admin, mobil_phone, icq, skype) VALUES (@email, @fname, @lname,@phone, @passhash, @createdon, @updatedon, @auth, @gadmin, @mobil, @icq, @skype);",connection);
                    command.Parameters.Add(new SqlParameter("@email", email));
                    command.Parameters.Add(new SqlParameter("@fname", fname));
                    command.Parameters.Add(new SqlParameter("@lname", lname));
                    command.Parameters.Add(new SqlParameter("@phone", phone));
                    command.Parameters.Add(new SqlParameter("@passhash", Utils.GetMd5Hash(pass)));
                    command.Parameters.Add(new SqlParameter("@createdon", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@updatedon", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@auth", Convert.ToByte(auth)));
                    command.Parameters.Add(new SqlParameter("@gadmin", gadmin));
                    command.Parameters.Add(new SqlParameter("@mobil", mobil));
                    command.Parameters.Add(new SqlParameter("@icq", icq));
                    command.Parameters.Add(new SqlParameter("@skype", skype));

                    command.ExecuteNonQuery();

                    //pridani uzivatele,ziskani poslendiho id a pridani role uzivateli
                    command = new SqlCommand("SELECT TOP (1) id FROM Users ORDER BY id DESC",connection);
                    reader = command.ExecuteReader();
                    reader.Read();
                    int lastid = Convert.ToInt32(reader["id"].ToString());
                    if (reader != null && reader.IsClosed == false)
                        reader.Close();

                    command = new SqlCommand("INSERT INTO User_Roles (user_id, role_id) VALUES (@user, @role)", connection);
                    command.Parameters.Add(new SqlParameter("@user", lastid));
                    command.Parameters.Add(new SqlParameter("@role", (gadmin==false) ? 3 : 2));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null) 
                    connection.Close();
            }

        }

        /// <summary>
        /// Vyhleda udaje o uzivateli
        /// </summary>
        /// <param name="id">id uzivatele</param>
        /// <returns>nalezeny uzivatel</returns>
        public UserObject SelectUserDetail(int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    UserObject o = new UserObject();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Users WHERE id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        o.Id = Convert.ToInt32(reader["id"]);
                        o.Email = reader["email"].ToString();
                        o.FirstName = reader["first_name"].ToString();
                        o.LastName = reader["last_name"].ToString();
                        o.Phone = reader["phone"].ToString();
                        o.LastLogin = (reader["last_login"] != DBNull.Value) ? (DateTime)reader["last_login"] : DateTime.MinValue;
                        o.PasswordHash = reader["password_hash"].ToString();
                        o.Lock = Convert.ToByte(reader["lock_version"]);
                        o.CreatedOn = (DateTime)reader["created_on"];
                        o.UpdatedOn = (DateTime)reader["updated_on"];
                        o.Authorization = Convert.ToByte(reader["authorization"]);
                        o.GalleryAdmin = Convert.ToByte(reader["gallery_admin"]);
                        o.MobilPhone = reader["mobil_phone"].ToString();
                        o.Icq = Convert.ToInt32(reader["icq"]);
                        o.Skype = reader["skype"].ToString();
                    }

                    return o;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new UserObject();
        }

        /// <summary>
        /// Provede update udaju o uzivateli
        /// </summary>
        /// <param name="o">uzivatel</param>
        public void UpdateUserDetail(UserObject o)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("UPDATE Users SET email=@email, first_name=@jmeno, last_name=@prijmeni, phone=@telefon, updated_on=@updated, [authorization]=@auth, gallery_admin=@gadmin, mobil_phone=@mobil, icq=@icq, skype=@skype WHERE id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@email", o.Email));
                    command.Parameters.Add(new SqlParameter("@jmeno", o.FirstName));
                    command.Parameters.Add(new SqlParameter("@prijmeni", o.LastName));
                    command.Parameters.Add(new SqlParameter("@telefon", (o.Phone == null)? "" : o.Phone));
                    command.Parameters.Add(new SqlParameter("@updated", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@auth", o.Authorization));
                    command.Parameters.Add(new SqlParameter("@gadmin", o.GalleryAdmin));
                    command.Parameters.Add(new SqlParameter("@mobil", (o.MobilPhone == null) ? "" : o.MobilPhone));
                    command.Parameters.Add(new SqlParameter("@icq", o.Icq));
                    command.Parameters.Add(new SqlParameter("@skype", (o.Skype == null) ? "" : o.Skype));
                    command.Parameters.Add(new SqlParameter("@id", o.Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Kontrola na jedinecnost emailu uzivatele v databazi
        /// </summary>
        /// <param name="email">email</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool EmailAlreadyExist(string email)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Users WHERE email=@email;", connection);
                    command.Parameters.Add(new SqlParameter("@email", email));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }

        /// <summary>
        /// Kontrola na jedinecnost emailu uzivatele v databazi
        /// </summary>
        /// <param name="email">email</param>
        /// <param name="user_id">id uzivatele</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool EmailAlreadyExist(string email, int user_id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Users WHERE email=@email AND id<>@user_id;", connection);
                    command.Parameters.Add(new SqlParameter("@email", email));
                    command.Parameters.Add(new SqlParameter("@user_id", user_id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }

        /// <summary>
        /// Autorizace pri prihlasovani
        /// </summary>
        /// <param name="name">email</param>
        /// <param name="pass">heslo</param>
        /// <returns>Objekt s informacemi o prihlasenem uzivateli</returns>
        public LoginInfo LogIn(string name, string pass) {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT Users.id, role_id FROM Users, User_Roles WHERE email=@email AND password_hash=@pass AND Users.id = User_Roles.user_id;", connection);
                    command.Parameters.Add(new SqlParameter("@email", name));
                    command.Parameters.Add(new SqlParameter("@pass", Utils.GetMd5Hash(pass)));

                    reader = command.ExecuteReader();

                    if (reader.HasRows) {
                        reader.Read();
                        //HttpContext.Current.Session.Timeout = 24*60;//prihlaseni na 24 hodin
                        
                        LoginInfo li = new LoginInfo(Convert.ToInt32(reader["id"]), (Utils.SystemRoles)reader["role_id"]);
                        /*HttpContext.Current.Session["logged_user_id"] = reader["id"];
                        HttpContext.Current.Session["roles"] = reader["role_id"];*/
                        if ((int)reader["role_id"] == (int)Utils.SystemRoles.Admin)
                        {
                            //HttpContext.Current.Session["admin_id"] = reader["id"];
                            li.AdminId = Convert.ToInt32(reader["id"]);
                        }

                        return li;
                    }
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return null;
        }

        /// <summary>
        /// Smaze uzivatele z databaze
        /// </summary>
        /// <param name="Id">id uzivatele</param>
        public void DeleteFromDB(int Id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("DELETE FROM Users WHERE (id = @id);", connection);
                    command.Parameters.Add(new SqlParameter("@id", Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }
    
        /// <summary>
        /// Nastavi uzivatele jako administratora
        /// </summary>
        /// <param name="Id">id uzivatele</param>
        public void SetAsAdmin(int Id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("UPDATE User_Roles SET role_id=1 WHERE user_id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@id", Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }
    }
}
